﻿/**********************************弘石
*******************************************
* 功能描述:系统设置（机构设置）
* 作者:李兰祥
* 时间:2021‎年‎12‎月‎23日
* 
**********************************弘石
********************************************/
using HongStone.Ince;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data.SqlClient;
using System.Data;
using HongStone.Domain.SystemSetupModel.InstitutionSettingModel;
using HongStone.Domain.SystemSetupModel.FrequentlyUsedDataModel;
using HongStone.Domain.AttendanceManagement;
using HongStone.Domain.SystemSetupModel.AuthorityManagement;

namespace HongStone.Repository
{
    public class InstitutionalSetting: InstitutionalSettingInce
    {
        private readonly HongStoneContext _app;
        public InstitutionalSetting(HongStoneContext app)
        {
            _app = app;
        }

        /// <summary>
        /// 分页显示,查询(部门管理)
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <param name="DeptName"></param>
        /// <returns></returns>
        public List<Department_category> GetDepartments(int pageIndex, int pageSize, ref int totalCount, string DeptName)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = $"select * from Department a left join DictionariesTable b on a.DicTableId = b.DictionariesId  where 1=1  ";
                if (!string.IsNullOrEmpty(DeptName))
                {
                    sql += $" and DeptName like '%{DeptName}%'";
                }

                var list = conn.Query<Department_category>(sql).ToList();

                totalCount = list.Count();

                sql += $" order by [Order] offset {(pageIndex - 1) * pageSize} rows fetch next {pageSize} rows only";


                var page = conn.Query<Department_category>(sql).ToList();

                return page;
            }
        }

        /// <summary>
        /// 添加部门(部门管理)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddDepartment(Department model)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"insert into Department values('{model.Dep_DeptId}','002','{model.DeptName}','{model.SuperiorDepartment}','{model.DeptIndex}','{model.DeptDesc}','{model.Order}','{model.StaffId}','{model.LeiXing}','50')");

                return list;
            }
        }

        /// <summary>
        /// 批量删除(部门管理)
        /// </summary>
        /// <returns></returns>
        public int DeleteDepartment(string ids)
        {
            var list = _app.departments.Where(u => ids.Contains(Convert.ToString(u.DeptId)));
            _app.departments.RemoveRange(list);

            return _app.SaveChanges();
        }

        /// <summary>
        /// 反填获取数据(部门管理)
        /// </summary>
        /// <param name="DeptId"></param>
        /// <returns></returns>
        public Department EditDepartment(int DeptId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Department>($"select * from Department where DeptId={DeptId}").FirstOrDefault();

                return list;
            }
        }

        /// <summary>
        /// 修改(部门管理)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateDepartment(Department model)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"update Department set DeptNumber='{model.DeptNumber}',DeptName='{model.DeptName}',LeiXing='{model.LeiXing}',DeptDesc='{model.DeptDesc}',[Order]='{model.Order}' where DeptId='{model.DeptId}'");

                return list;
            }
        }

        /// <summary>
        /// 修改状态(显示)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateDepartment01(int DicTableId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"update DictionariesTable set IsDelete='1' where DictionariesId={DicTableId}");

                return list;
            }
        }

        /// <summary>
        /// 修改状态(隐藏)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateDepartment02(int DicTableId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"update DictionariesTable set IsDelete='0' where DictionariesId={DicTableId}");

                return list;
            }
        }

        /// <summary>
        /// 提醒管理+分页查询
        /// </summary>
        /// <param name="BuName"></param>
        /// <param name="Name"></param>
        /// <returns></returns>
        public List<Reminder> GetReminders(string BuName, string Name,int pageIndex,int pageSize,ref int totalCount)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = $"select a.ReminderId,c.DictionariesName,b.LoginName,a.Datatimes,a.Zhuan,a.DaoQi,DATEDIFF(DD,a.DaoQi,d.Contractsigningdate) as 'HeTime',DATEDIFF(DD,a.Datatimes,a.Zhuan) as 'ZhuanTime' from Reminder a join[Login] b on a.LoginId = b.LoginId join DictionariesTable c on a.DictionariesId = c.DictionariesId join Thecontract d on a.Thecontract = d.ContractId where c.IsDelete = 'true' ";
                if (!string.IsNullOrEmpty(BuName))
                {
                    sql += $" and c.DictionariesName='{BuName}' ";
                }
                if (!string.IsNullOrEmpty(Name))
                {
                    sql += $" and b.LoginName like '%{Name}%' ";
                }

                var list = conn.Query<Reminder>(sql).ToList();

                totalCount = list.Count();

                sql += $" order by a.ReminderId offset {(pageIndex - 1) * pageSize} rows fetch next {pageSize} rows only";

                var page = conn.Query<Reminder>(sql).ToList();

                return page;
            }
        }

        /// <summary>
        ///显示权限（模块权限设置）
        /// </summary>
        /// <param name="ModuleGrade"></param>
        /// <returns></returns>
        public List<Function> GetFuntion(string ModuleGrade)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = $"select * from [Function] where 1=1 ";
                if (!string.IsNullOrEmpty(ModuleGrade))
                {
                    sql += $" and ModuleGrade='{ModuleGrade}'";
                }

                var list = conn.Query<Function>(sql).ToList();

                return list;
            }
        }

        /// <summary>
        /// 添加模块（模块权限设置）
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(Function model)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"insert into [Function] values('{model.ModuleId}','{model.ModuleName}','{model.ModuleGrade}','1','{model.Url}','0',GETDATE(),'{model.Yuan}')");

                return list;
            }
        }

        /// <summary>
        /// 反填模块（模块权限设置）
        /// </summary>
        /// <param name="ModuleId"></param>
        /// <returns></returns>
        public Function GetFunctions(string ModuleId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Function>($"select * from [Function] where ModuleId='{ModuleId}'").FirstOrDefault();

                return list;
            }
        }

        /// <summary>
        /// 修改模块（模块权限设置）
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateFuntion(Function model)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"update [Function] set ModuleName='{model.ModuleName}',ModuleGrade='{model.ModuleGrade}',Url='{model.Url}' where ModuleId='{model.ModuleId}'");

                return list;
            }
        }

        /// <summary>
        /// 删除模块（模块权限设置）
        /// </summary>
        /// <param name="ModuleId"></param>
        /// <returns></returns>
        public int DeleteFunctions(string ModuleId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"delete from [Function] where ModuleId='{ModuleId}'");

                return list;
            }
        }

        /// <summary>
        /// 功能权限设置显示
        /// </summary>
        /// <param name="ModuleGrade"></param>
        /// <returns></returns>
        public List<Function_Roles> GetFunction_Roles(string ModuleGrade)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = $"select * from[Function] b left join Function_Role a on a.FunctionID = b.ModuleId left join RoleAdministrator c on a.RoleID = c.RoleId where 1 = 1 ";

                if (!string.IsNullOrEmpty(ModuleGrade))
                {
                    sql += $" and b.ModuleGrade='{ModuleGrade}'";
                }

                var list = conn.Query<Function_Roles>(sql).ToList();

                return list;
            }
        }

        /// <summary>
        /// 显示角色
        /// </summary>
        /// <returns></returns>
        public List<RoleAdministrator> GetRoleAdministrators()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<RoleAdministrator>($"select * from RoleAdministrator").ToList();

                return list;
            }
        }

        /// <summary>
        /// 添加权限（添加功能权限）
        /// </summary>
        /// <returns></returns>
        public int AddFunction_Role(Function_Role model)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                //添加之前清空
                var page = $"select * from Function_Role where RoleID={model.RoleID} ";
                if (page != null)
                {
                    conn.Execute($"delete from Function_Role where RoleID={model.RoleID}");
                }

                var list = conn.Execute($"insert into Function_Role values('{model.FunctionID}','{model.RoleID}','{model.LoginName}')");

                return list;
            }
        }

        /// <summary>
        /// 反填(功能权限)
        /// </summary>
        /// <param name="ModuleId"></param>
        /// <returns></returns>
        public Function_Roles EditFunction_Roles(string ModuleId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Function_Roles>($"select * from Function_Role a join [Function] b on a.FunctionID = b.ModuleId join RoleAdministrator c on a.RoleID=c.RoleId where FunctionID = '{ModuleId}'").FirstOrDefault();

                return list;
            }
        }

        /// <summary>
        /// 修改（功能权限）
        /// </summary>
        /// <returns></returns>
        public int UpdateFunction_Role(RoleAdministrator model)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var page = conn.Execute($"update RoleAdministrator set RoleName='{model.RoleName}' where RoleId='{model.RoleId}'");

                return page;
            }
        }

        /// <summary>
        /// 显示（角色权限设置）
        /// </summary>
        /// <returns></returns>
        public List<Function_Roles> GetRole()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Function_Roles>("select RoleName,stu.RoleId,STUFF((select ',' +c.LoginName from RoleAdministrator a join Role_Login b on a.RoleId = b.RoleId join Login c on b.LoginId = c.LoginId where a.RoleName = stu.RoleName for xml path('')),1,1,'')names from RoleAdministrator stu right join Role_Login b on stu.RoleId = b.RoleId group by RoleName,stu.RoleId order by RoleName").ToList();
                return list;
            }
        }

        /// <summary>
        /// 添加角色管理
        /// </summary>
        /// <returns></returns>
        public List<Role_Login> AddRoless(int RoleId,string LoginId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {

                    //打开
                    conn.Open();
                    using (var begin = conn.BeginTransaction())
                    {
                        try
                        {
                            var model = LoginId.Split(',');
                            foreach (var item in model)
                            {
                                conn.Execute($"insert into Role_Login values('{RoleId}','{item}')", null, begin);
                            }
                            begin.Commit();
                            List<Role_Login> page = new List<Role_Login>();
                            return page;
                        }
                        catch (Exception)
                        {
                            begin.Rollback();
                            throw;
                        }
                    }
            }
        }

        /// <summary>
        /// 保存【添加角色管理】
        /// </summary>
        /// <returns></returns>
        public int AddInfos(Role_Login model)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"insert into Role_Login values('{model.RoleId}','{model.LoginId}')");

                return list;
            }
        }

        /// <summary>
        /// 显示用户
        /// </summary>
        /// <returns></returns>
        public List<Login> GetLogin()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Login>("select * from Login").ToList();

                return list;
            }
        }

        /// <summary>
        /// 反填
        /// </summary>
        /// <returns></returns>
        public Role_Login EditRole_Login(int RoleId) 
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Role_Login>($"select * from Role_Login where RoleId='{RoleId}'").FirstOrDefault();

                return list;
            }
        }

        /// <summary>
        /// 修改角色管理
        /// </summary>
        /// <returns></returns>
        public List<Role_Login> UpdateRoless(int RoleId, string LoginId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                //添加之前清空
                var list = $"select * from Role_Login where RoleId={RoleId} ";
                if (list != null)
                {
                    conn.Execute($"delete from Role_Login where RoleId={RoleId}");
                }

                //打开
                conn.Open();
                using (var begin = conn.BeginTransaction())
                {
                    try
                    {
                        var model = LoginId.Split(',');
                        foreach (var item in model)
                        {
                            conn.Execute($"insert into Role_Login values('{RoleId}','{item}')", null, begin);
                        }
                        begin.Commit();
                        List<Role_Login> page = new List<Role_Login>();
                        return page;
                    }
                    catch (Exception)
                    {
                        begin.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}
